﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using dhDataAcess.sysDAL;

namespace dhDataAcess.baseBLL
{
  public   class dTableColumnRead
    {
        string tableName;

        static string tableSql = "  select" +
                         "[表名]=c.Name," +
                         "[表说明]=isnull(f.[value],'')," +
                         "[列名]=a.Name," +
                         "[列序号]=a.Column_id," +
                         "[标识]=case when is_identity=1 then '1' else '0' end," +
                         "[主键]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in" +
                         "(select Name from sys.indexes where index_id in" +
                         "(select indid from sysindexkeys where  colid=a.column_id and id=a.object_id )))" +
                         "then '1' else '0' end," +
                         "[类型]=b.Name," +
                         "[字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'" +
                         "when b.Name='xml' then ' 2^31-1字节/2G'" +
                         "else rtrim(a.[max_length]) end," +
                         "[长度]=ColumnProperty(a.object_id,a.Name,'Precision')," +
                         "[小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0)," +
                         "[是否為空]=case when a.is_nullable=1 then '√' else '' end," +
                         "[列说明]=isnull(e.[value],'')," +
                         "[默认值]=isnull(d.text,'')" +
                         " from" +
                         " sys.columns a" +
                         " left join" +
                         " sys.types b on a.user_type_id=b.user_type_id" +
                         " inner join" +
                         " sys.objects c on a.object_id=c.object_id and c.Type='U'" +
                         " left join" +
                         " syscomments d on a.default_object_id=d.ID" +
                         " left join" +
                         " sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1" +
                         " left join" +
                         " sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 ";

        public dTableColumnRead(string _tableName)
        {
            tableName = _tableName;
        }

        public static string insertSqlStr(string tableName)
        {
            SqlConnection conn = dataConn.connOpen();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='" + tableName + "' AND  COLUMNPROPERTY(OBJECT_ID('" + tableName + "'),COLUMN_NAME,'IsIdentity')!=1";
            SqlDataReader rdr = cmd.ExecuteReader();
            string str = "insert into " + tableName + "(";
            string strValue = " values(";
            List<string> filedStr = new List<string>();
            while (rdr.Read())
            {
                filedStr.Add(rdr[0].ToString());
            }

            foreach (string field in filedStr)
            {
                str = str + field + ",";
                strValue = strValue + "@" + field + ",";
            }

            return str.Substring(0, str.Length - 1) + ") " + strValue.Substring(0, strValue.Length - 1) + ")";


        }

        public static string updateSqlStr(string tableName)
        {
            SqlConnection conn = dataConn.connOpen();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = tableSql + " where c.name='" + tableName + "'";
            SqlDataReader rdr = cmd.ExecuteReader();
            string str = "update " + tableName + " set ";
            //string strValue = " values(";
            List<columnList> listAll = new List<columnList>();
            while (rdr.Read())
            {
                columnList ll = new columnList();
                ll.columnName = rdr[2].ToString();
                ll.ident = rdr[4].ToString();
                ll.priamKey = rdr[5].ToString();
                listAll.Add(ll);
            }

            string primKey = "";
            foreach (columnList field in listAll)
            {
                if (field.ident != "1")
                    str = str + field.columnName + "=@" + field.columnName + ",";
                if (field.priamKey == "1")
                    primKey = field.columnName;
                //strValue = strValue + "@" + field + ",";
            }

            return str.Substring(0, str.Length - 1) + " where " + primKey + "=@" + primKey;
        }
        public static string delSqlStr(string tableName)
        {
            SqlConnection conn = dataConn.connOpen();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = tableSql + " where c.name='" + tableName + "'";
            SqlDataReader rdr = cmd.ExecuteReader ();
            string str = "delete " + tableName ;
            List<columnList> listAll = new List<columnList>();
            while (rdr.Read() )
            {
                columnList ll = new columnList();
                ll.columnName = rdr[2].ToString();
                ll.priamKey = rdr[5].ToString();
                listAll.Add(ll);
            }
            string primKey = "";
            foreach (columnList field in listAll)
            {
                if (field.priamKey == "1")
                    primKey  = field.columnName;
            }
            return str + " where " + primKey + "=@" + primKey;    
            }
        }



    }

